/*
 * Created on 2006-2-18
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.hitrust.gs.shipment;

import java.io.File;
import jxl.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.util.lang.IDGenerator;
/**
 * @author simonyi
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class GSShipmentManager {

    public File getShipmentFile(String fileNameWithPath){
        File f = new File(fileNameWithPath);
        
        return f;
    }
    

    /**
     * 从excel文件读取集中采购系统提交给采购网的出货单信息
     * @param tFile
     */
    public HashMap getShipment(File tFile) throws GSException{
        Object[] obj = null;
        HashMap map = new HashMap();
        try{
            //开始读取excel文件
            Workbook tBook = Workbook.getWorkbook(tFile);
            //读取sheet表单
            Sheet tSheet = tBook.getSheet(0);
            
            Cell[] frCells = tSheet.getRow(0);
            //xls文件，只有5列
            int index = 0;
            int rowslength = tSheet.getRows();
            //System.out.println(rowslength);//
            while(true){                
                if(index == rowslength-1 ){
                    break;
                }

                Cell[] cells = tSheet.getRow(++index);
                GSShipmentBean tBean = new GSShipmentBean();
                tBean.concentricityId = cells[0].getContents();
                System.out.println(tBean.concentricityId + "-" + index);
                tBean.manufactureId = cells[1].getContents();
                tBean.providerId = cells[2].getContents();
                tBean.shipmentOrderId = cells[3].getContents();
                tBean.deptName =  cells[4].getContents();
                tBean.sendMode = cells[5].getContents();
                tBean.payMode = cells[6].getContents();
                tBean.receiveDate = cells[7].getContents();
                tBean.receivePerson = cells[8].getContents();
                tBean.receiveNum = cells[9].getContents();
                tBean.unitPrice = cells[10].getContents();
                tBean.receivePrice = cells[11].getContents();
                tBean.inputDate = cells[12].getContents();
                if(cells.length == 13);
                else
                    tBean.remark = cells[13].getContents();
                if(map.containsKey(tBean.deptName)){
                    ArrayList temp = (ArrayList)map.get(tBean.deptName);
                    temp.add(tBean);
                }else{
                    ArrayList temp = new ArrayList();
                    temp.add(tBean);
                    map.put(tBean.deptName,temp);
                }
            }
        }
        catch(Exception e){
            throw new GSException(GSException.E17790,GSException.E17790_MSG);
        }
        return map;
    }
    
    
    
    /**
     * 导入数据到数据库(出货单)
     * @param tFile
     * @param category
     * @throws GSException
     */
    public boolean saveShipment(File tFile,String date) throws GSException {

        Connection conn = null;
        HashMap map = getShipment(tFile);
        if(map.isEmpty()){
            return false;
        }
        boolean returnValue = false; 

        try{
            conn = new GSBaseDAO().getConnetion();
            conn.setAutoCommit(false);

            
            IDGenerator idg = new IDGenerator();

            
            //1 构造生成时间
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String createDate = sdf.format(new Date(System.currentTimeMillis()));
            
            //2 构造订单明细sql语句
            PreparedStatement pstmt = null;
            String sqlm = "insert into GS_ORDER_TEMP_DETAIL (" +
            "GS_ORDER_TEMPID,GS_ORDER_DETAIL_ID,GS_ORDER_CATEGORY,GS_WORKCORP_ID," +
        	"GS_DELIVER_GOODS_ADDR,GS_DELIVER_MODE,GS_BALANCE_MODE,GS_ORDER_CONTACT_NAME," +
        	"GS_ORDER_CONTACT_PHONE,GS_CREATE_TIME,GS_ORDER_DETAIL_STATUS,GS_CONCENTRICITY_ID," +
        	"GS_PROVIDER_ID,GS_MANUFACTURE_ID,GS_GOODS_UNITPRICE,GS_DELIVER_GOODS_ID," +
        	"GS_SIGNIN_DATE,GS_ORDER_COUNT,GS_SIGNIN_COUNT,GS_SIGNIN_PRICE," +
        	"GS_SIGNIN_PERSON,GS_SIGNIN_EXPLAIN,GS_LASTMODIFER_ID,GS_LASTMODIFY_CONTENTS," +
        	"GS_LASTMODIFY_DATE,GS_RECORDER_ID" +
        	") values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(sqlm);
            
            Iterator it = map.keySet().iterator();
            while(it.hasNext()){
                String deptName = (String)it.next();
                String[] deptMsg = getDeptMsg(deptName);//机构号，机构名，地址号，地址，联系人，联系电话
                
                ArrayList ary = (ArrayList)map.get(deptName);
                for(int i=0;i<ary.size();i++){
                    GSShipmentBean tBean = (GSShipmentBean)ary.get(i);
                    
                    //构造订单号
                    long id = idg.generatorID(deptMsg[0],"02");
                    String orderId = deptMsg[0] + idg.formatID(id,4) + "L";
                    String orderDetailId = orderId + idg.formatID(i,2);
                    
                    pstmt.setString(1,orderId);
                    pstmt.setString(2,orderDetailId);
                    pstmt.setString(3,"1");
                    pstmt.setString(4,deptMsg[0]);
                    pstmt.setString(5,deptMsg[3]);
                    pstmt.setString(6,tBean.sendMode);
                    pstmt.setString(7,tBean.payMode);
                    pstmt.setString(8,deptMsg[4]);
                    pstmt.setString(9,deptMsg[5]);
                    pstmt.setString(10,createDate);
                    pstmt.setString(11,"1");
                    pstmt.setString(12,tBean.concentricityId);
                    pstmt.setString(13,tBean.providerId);
                    pstmt.setString(14,tBean.manufactureId);
                    pstmt.setString(15,tBean.unitPrice);
                    pstmt.setString(16,tBean.shipmentOrderId);
                    pstmt.setString(17,tBean.receiveDate);
                    pstmt.setString(18,tBean.receiveNum);
                    pstmt.setString(19,tBean.receiveNum);
                    pstmt.setString(20,tBean.receivePrice);
                    pstmt.setString(21,tBean.receivePerson);
                    pstmt.setString(22,tBean.remark);
                    pstmt.setString(23,"gssystem");
                    pstmt.setString(24,"出货单导入");
                    pstmt.setString(25,createDate);
                    pstmt.setString(26,"gssystem");
                    
                    pstmt.addBatch();
                }
            }

            pstmt.executeBatch();
            pstmt.close();
            
            returnValue = this.updateProcessStatus(conn,date);
            
            conn.commit();
            
        }catch(SQLException sqle){
            try{
                conn.rollback();    
            }catch(SQLException sqlex){
                
            }            
            throw new GSException (GSException.E19998,GSException.E19998_MSG);
        }catch(Exception e){
            try{
                conn.rollback();    
            }catch(SQLException sqlex){
                
            }            
            throw new GSException (GSException.E19998,GSException.E19998_MSG);
        }
        finally{
            try{
                conn.setAutoCommit(true);
            }catch(SQLException sqle){}
            GSBaseDAO.close(conn);
        }
        
        return returnValue;
    }
    
    private String[] getDeptMsg(String orgName) throws GSException{
        GSBaseDAO dao = new GSBaseDAO();
        Connection conn = null;
        String[] str = new String[6];
        try{
            conn = dao.getConnetion();;
            String tSql = "select a.gs_org_id,a.gs_org_name,b.gs_deliver_goods_addr_id," +
            		"b.gs_deliver_goods_addr,b.gs_order_contact_name,b.gs_order_contact_phone " +
            		"from gs_organization a,gs_order_dept_info b " +
            		"where a.gs_org_id = b.gs_org_id and a.gs_org_name=?";
            PreparedStatement pstmt = conn.prepareStatement(tSql);
            pstmt.setString(1,orgName);
            ResultSet rs = pstmt.executeQuery();
            if(rs!=null){
                rs.next();
                
                str[0] = rs.getString(1);
                str[1] = rs.getString(2);
                str[2] = rs.getString(3);
                str[3] = rs.getString(4);
                str[4] = rs.getString(5);
                str[5] = rs.getString(6);
                
                rs.close();
            }
            pstmt.close();
            return str;
        }catch(Exception e){
            throw new GSException(GSException.E00001,GSException.E00001_MSG);
        }finally{
            GSBaseDAO.close(conn);
        }
    }
    
    public boolean isProcessSucceed(String date) throws GSException {
        GSBaseDAO dao = new GSBaseDAO();
        Connection conn = null;
        String str = "";
        int size = 0;
        try{
            conn = dao.getConnetion();
            conn.setAutoCommit(false);
            ResultSet rs = null;
            String tSql0 = "select count(*) from gs_shipmentparam where gs_shipmentdate = ?";
            String tSql1 = "select * " +
            		"from gs_shipmentparam " +
            		"where gs_shipmentdate = ?";
            String tSql2 = "insert into gs_shipmentparam (GS_SHIPMENTDATE,GS_STATUS) VALUES (?,?)";
            
            //察看是否有状态纪录,如果没有,增加状态纪录并返回未成功
            PreparedStatement pstmt0 = conn.prepareStatement(tSql0);
            PreparedStatement pstmt2 = conn.prepareStatement(tSql2);
            pstmt0.setString(1,date);
            pstmt2.setString(1,date);
            pstmt2.setString(2,"1");
            rs = pstmt0.executeQuery();
            if(rs!=null){
                rs.next();
                size = rs.getInt(1);
            }
            rs.close();
            if(size == 0){
                pstmt2.executeUpdate();
                pstmt2.close();
                conn.commit();
                return false;
            }
            
            //否则的话,看状态记录的值,根据状态纪录返回成功或失败
            PreparedStatement pstmt1 = conn.prepareStatement(tSql1);
            pstmt1.setString(1,date);
            rs = pstmt1.executeQuery();
            
            if(rs!=null){
                rs.next();
                str = rs.getString(2);
                rs.close();
            }
            pstmt1.close();

            if("0".equals(str))
                return true;
            else return false;
        }catch(Exception e){
            throw new GSException(GSException.E00001,GSException.E00001_MSG);
        }finally{
            try{
                conn.setAutoCommit(true);    
            }catch(Exception e){
            }
            GSBaseDAO.close(conn);
        }
    }
    
    public boolean updateProcessStatus(Connection conn,String date) throws GSException  {

        String str = "";
        try{
            String tSql = "update gs_shipmentparam set gs_status = '0' " +
            		"where gs_shipmentdate = ?";
            PreparedStatement pstmt = conn.prepareStatement(tSql);
            pstmt.setString(1,date);
            pstmt.executeUpdate();
            pstmt.close();

            return true;
        }catch(Exception e){
            throw new GSException(GSException.E00001,GSException.E00001_MSG);
        }
        
    }
}


